# 5_层叠柱形图.py
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import matplotlib.patches as patches
import os
from matplotlib.colors import LinearSegmentedColormap
from datetime import datetime

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 定义常量 - 保存结果的目录
RESULTS_DIR = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\results"


def load_erp_data():
    """加载ERP订单数据"""
    # 定义可能的文件路径
    possible_paths = [
        r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\data\erp_order_data.xlsx",
        r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\erp_order_data.xlsx"
    ]

    for path in possible_paths:
        if os.path.exists(path):
            try:
                df = pd.read_excel(path)
                # 确保日期格式正确
                if 'order_time' in df.columns:
                    df['order_time'] = pd.to_datetime(df['order_time'])
                print(f"✓ 数据加载成功: {path}")
                return df
            except Exception as e:
                print(f"读取 {path} 失败: {e}")
                continue

    raise FileNotFoundError("未找到erp_order_data.xlsx文件，请确认文件是否存在或路径是否正确")


def process_quarterly_data(df):
    """处理季度数据"""
    # 从订单时间提取年份和季度
    df['year'] = df['order_time'].dt.year
    df['quarter'] = df['order_time'].dt.quarter
    df['quarter_str'] = df['year'].astype(str) + 'Q' + df['quarter'].astype(str)

    # 按季度汇总销售额
    quarterly_sales = df.groupby('quarter_str')['product_amount'].sum().reset_index()
    quarterly_sales.columns = ['quarter', 'sales']

    # 假设利润率为35%(基于行业标准电商利润率)
    # 仅使用数据集中的真实数据计算，不添加额外数据
    quarterly_sales['profit'] = quarterly_sales['sales'] * 0.35

    # 按时间顺序排序
    quarterly_sales = quarterly_sales.sort_values('quarter')

    # 仅保留数据集中实际存在的季度
    return quarterly_sales


def create_stacked_barchart():
    """创建层叠柱形图 - 展示季度销售额与利润"""
    # 加载数据
    df = load_erp_data()

    # 处理季度数据
    quarterly_data = process_quarterly_data(df)

    # 提取数据，只使用数据集中实际存在的季度
    quarters = quarterly_data['quarter'].tolist()
    sales = quarterly_data['sales'].tolist()
    profits = quarterly_data['profit'].tolist()

    # 创建渐变颜色映射（蓝色用于销售额，红色用于利润）
    sales_colors = ['#16213E', '#0F3460', '#1A5F7A', '#2C8C99', '#4BB5C2', '#7FD6E3']
    profit_colors = ['#5C1E2B', '#8A2D3D', '#B73E4F', '#D54D5F', '#E56A72', '#F08B8F']

    # 创建颜色映射
    sales_cmap = LinearSegmentedColormap.from_list('sales_blue', sales_colors, N=100)
    profit_cmap = LinearSegmentedColormap.from_list('profit_red', profit_colors, N=100)

    # 创建图形 - 靛蓝色背景
    fig = plt.figure(figsize=(14, 9), facecolor='#1A1A2E')
    ax = fig.add_subplot(111, facecolor='#1A1A2E')

    # 设置柱子宽度和位置
    bar_width = 0.6
    x_pos = np.arange(len(quarters))

    # 绘制销售额部分（蓝色）
    for i, (x, sale) in enumerate(zip(x_pos, sales)):
        gradient_height = 80
        for j in range(gradient_height):
            height_segment = sale / gradient_height
            y_bottom = j * height_segment
            color = sales_cmap(1 - (j / gradient_height) * 0.7)
            rect = patches.Rectangle(
                (x - bar_width / 2, y_bottom), bar_width, height_segment,
                linewidth=0, facecolor=color, alpha=0.9
            )
            ax.add_patch(rect)

    # 绘制利润部分（红色）- 层叠在销售额之上
    for i, (x, profit) in enumerate(zip(x_pos, profits)):
        gradient_height = 80
        for j in range(gradient_height):
            height_segment = profit / gradient_height
            y_bottom = sales[i] + j * height_segment
            color = profit_cmap(1 - (j / gradient_height) * 0.7)
            rect = patches.Rectangle(
                (x - bar_width / 2, y_bottom), bar_width, height_segment,
                linewidth=0, facecolor=color, alpha=0.9
            )
            ax.add_patch(rect)

    # 添加白色边框
    for i in x_pos:
        ax.bar([i], [sales[i]], width=bar_width, color='none', edgecolor='#E0E0E0', linewidth=1.5)
        ax.bar([i], [profits[i]], bottom=sales[i], width=bar_width, color='none', edgecolor='#E0E0E0', linewidth=1.5)

    # 添加数据标签
    for i, (sale, profit) in enumerate(zip(sales, profits)):
        # 销售额标签
        ax.text(i, sale * 0.95, f'{int(sale)}', ha='center', va='center',
                fontsize=12, fontweight='bold', color='#FFFFFF',
                bbox=dict(boxstyle='round,pad=0.2', facecolor='#2C3E50', edgecolor='none', alpha=0.7))

        # 利润标签
        ax.text(i, sale + profit * 0.5, f'{int(profit)}', ha='center', va='center',
                fontsize=12, fontweight='bold', color='#FFFFFF',
                bbox=dict(boxstyle='round,pad=0.2', facecolor='#5C1E2B', edgecolor='none', alpha=0.7))

    # 根据数据生成标题
    latest_date = df['order_time'].max()
    earliest_date = df['order_time'].min()
    title_main = f'{earliest_date.year}年{earliest_date.month}月-{latest_date.year}年{latest_date.month}月季度销售额(万)和利润额(万)'

    # 分析最近季度的趋势
    if len(quarters) >= 2:
        last_quarter_sales = sales[-1]
        prev_quarter_sales = sales[-2]
        change_percent = (last_quarter_sales - prev_quarter_sales) / prev_quarter_sales * 100
        if change_percent < 0:
            subtitle = f'{quarters[-1]}销售额环比下降{abs(change_percent):.1f}%'
        else:
            subtitle = f'{quarters[-1]}销售额环比增长{change_percent:.1f}%'
    else:
        subtitle = '数据展示各季度销售与利润情况'

    ax.set_title(f'{title_main}\n{subtitle}',
                 fontsize=20, fontweight='bold', pad=25, color='#FFFFFF')

    # 设置坐标轴
    ax.set_ylabel('金额 (万元)', fontsize=18, fontweight='bold', color='#E0E0E0')
    ax.set_xlabel('季度', fontsize=16, fontweight='bold', color='#E0E0E0')
    ax.set_xticks(x_pos)
    ax.set_xticklabels(quarters, fontsize=14, fontweight='bold', color='#E0E0E0')
    ax.tick_params(axis='y', labelsize=14, colors='#E0E0E0')

    # 美化坐标轴
    for spine in ax.spines.values():
        spine.set_color('#4A4A6A')
        spine.set_linewidth(2)

    # 设置网格线
    ax.grid(axis='y', alpha=0.3, linestyle='--', color='#4A4A6A')
    ax.set_axisbelow(True)

    # 设置y轴范围
    max_value = max([s + p for s, p in zip(sales, profits)]) * 1.15
    ax.set_ylim(0, max_value)

    # 添加图例
    sales_patch = patches.Rectangle((0, 0), 1, 1, facecolor=sales_colors[-1], edgecolor='#E0E0E0')
    profit_patch = patches.Rectangle((0, 0), 1, 1, facecolor=profit_colors[-1], edgecolor='#E0E0E0')
    ax.legend([sales_patch, profit_patch], ['销售额', '利润额'],
              fontsize=14, frameon=False, loc='upper right',
              labelcolor='#E0E0E0', bbox_to_anchor=(0.98, 0.95))

    # 添加数据来源
    latest_date_str = latest_date.strftime('%Y.%m.%d')
    source_text = f'*注：数据来源于公司销售系统，统计日期截至{latest_date_str}'
    ax.text(0.02, 0.02, source_text, transform=ax.transAxes,
            fontsize=10, color='#B0B0B0', alpha=0.7, va='bottom')

    # 调整布局
    plt.tight_layout()

    # 确保结果目录存在
    os.makedirs(RESULTS_DIR, exist_ok=True)
    # 保存图片
    output_path = os.path.join(RESULTS_DIR, '05_层叠柱形图.png')
    plt.savefig(output_path, dpi=300, bbox_inches='tight',
                facecolor='#1A1A2E', edgecolor='none')

    plt.show()

    # 数据分析
    print("层叠柱形图数据分析：")
    print(f"- 数据覆盖周期：{len(quarters)}个季度，从{quarters[0]}到{quarters[-1]}")
    print(f"- 总销售额：{sum(sales):.2f}万元")
    print(f"- 总利润：{sum(profits):.2f}万元")
    print(f"- 平均利润率：{sum(profits) / sum(sales) * 100:.1f}%")
    if len(quarters) >= 2:
        print(f"- 最近季度({quarters[-1]})销售额：{sales[-1]:.2f}万元")
        print(f"- 环比变化：{((sales[-1] - sales[-2]) / sales[-2] * 100):.1f}%")

    return fig, ax


# 执行代码
if __name__ == "__main__":
    fig, ax = create_stacked_barchart()
